﻿Imports MySql.Data.MySqlClient

Public Class frmSupplyInfo
    Public New_Entry_State As Boolean
    Dim Strsql As String
    Dim mysql As New ClsSQLhelper
    Dim Cboonload As Boolean = True
    Dim On_load As Boolean = True
    Dim DS_AGENT As New DataSet
    Dim DA_AGENT As New MySqlDataAdapter
    Dim DS_Products As New DataSet
    Dim DA_Products As New MySqlDataAdapter
    Dim DSWHIS As New DSWHIS

#Region "Funtion"

    Sub Load_VendorData()
    
        Strsql = "select companyid,companycode,companyname,companytype,contactname,address1,tmppart,amppart,chwpart,zipcode,taxid,tel,fax,note,active from vendorcompany "
        If Me.New_Entry_State = False Then
            Strsql = Strsql & "    where companyid = " & Me.TxtID.Text
            Me.TxtCode.ReadOnly = True
            Me.BtnDelete.Enabled = True
        Else
            Strsql = Strsql & "    where companyid = 0"
        End If
        Dim DS As New DataSet
        DS = mysql.GetMYSQLDataset(Strsql, "Vendor", "vendorcompany")
        With DS.Tables("vendorcompany")
            Me.TxtID.Text = .Rows(0).Item("companyid").ToString
            Me.TxtCode.Text = .Rows(0).Item("companycode").ToString
            Me.txtCompanyname.Text = .Rows(0).Item("companyname").ToString
            Me.CboCompanyType.SelectedValue = .Rows(0).Item("companytype")
            Me.TxtContactname.Text = .Rows(0).Item("contactname").ToString
            Me.TxtTaxID.Text = .Rows(0).Item("taxid").ToString
            Me.TxtTel1.Text = .Rows(0).Item("tel").ToString
            Me.TxtFax1.Text = .Rows(0).Item("fax").ToString
            Me.TxtNote.Text = .Rows(0).Item("note").ToString
            Me.ChkActive.Checked = .Rows(0).Item("active").ToString
            Me.TxtAdd1.Text = .Rows(0).Item("address1").ToString
            Me.cboTmb.SelectedValue = .Rows(0).Item("tmppart")
            Me.cboAMPHUR.SelectedValue = .Rows(0).Item("amppart")
            Me.CboProvince.SelectedValue = .Rows(0).Item("chwpart")
            Me.TxtZipcode.Text = .Rows(0).Item("zipcode").ToString

        End With

    End Sub
    Function Validate_VendorDetail() As Boolean
        Try
            Validate_VendorDetail = False
            If Me.txtCompanyname.Text.Trim = "" Then
                MsgBox("กรุณากรอกชื่อบริษัท", MsgBoxStyle.Exclamation, "ข้อมูลไม่ครบถ้วน")
                Exit Function
            End If


            Validate_VendorDetail = True
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function

    Function Load_ComboboxData_site() As Boolean
        Try
            Dim DS As New DataSet

            Load_ComboboxData_site = False

            Strsql = "select * from site order by site"

            DS = mysql.GetMYSQLDataset(Strsql, "DS_site", "site")
            ' Me.EventLog1.WriteEntry(Strsql, EventLogEntryType.Information)
            With Me.CboSite
                If DS.Tables(0).Rows.Count = 0 Then Exit Function
                .DataSource = DS.Tables(0)
                .DisplayMember = "site"
                .ValueMember = "siteid"
                .SelectionStart = 0
                If My.Settings.CurrentSite <> "" Then
                    .SelectedValue = My.Settings.CurrentSite
                End If
            End With
            Load_ComboboxData_site = True
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
    End Function
    Function Load_ComboboxData_CompanyType() As Boolean
        Try
            Dim DS As New DataSet

            'Load_ComboboxData_site = False

            Strsql = "select vendor_type_id,vendor_type_name from vendorcompanytype order by vendor_type_id"

            DS = mysql.GetMYSQLDataset(Strsql, "DSvendor_type", "vendorcompanytype")
            ' Me.EventLog1.WriteEntry(Strsql, EventLogEntryType.Information)
            With Me.CboCompanyType
                If DS.Tables(0).Rows.Count = 0 Then Exit Function
                .DataSource = DS.Tables(0)
                .DisplayMember = "vendor_type_name"
                .ValueMember = "vendor_type_id"
                .SelectionStart = 0
            End With
            ' Load_ComboboxData_site = True
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
    End Function
    Sub Load_AddressData(Optional ByVal siteid As Integer = 0, Optional ByVal Str_Search As String = "")
        Try

            Dim DSProvince, DSAMPHUR, DSTmb As New DataSet


            DSProvince = mysql.GetMYSQLDataset("Select PROVINCE_ID,PROVINCE_NAME from tblprovince", "Dataset3", "tblprovince")
            With Me.CboProvince
                .DropDownStyle = ComboBoxStyle.DropDownList
                .DataSource = DSProvince.Tables(0)
                .DisplayMember = "PROVINCE_NAME"
                .ValueMember = "PROVINCE_ID"
            End With
            DSAMPHUR = mysql.GetMYSQLDataset("Select AMPHUR_ID,AMPHUR_NAME from tblAMPHUR where PROVINCE_ID = " & Me.CboProvince.SelectedValue.ToString, "Dataset5", "tblAMPHUR")

            With Me.cboAMPHUR
                .DropDownStyle = ComboBoxStyle.DropDownList
                .DataSource = DSAMPHUR.Tables(0)
                .DisplayMember = "AMPHUR_NAME"
                .ValueMember = "AMPHUR_ID"
            End With
            DSTmb = mysql.GetMYSQLDataset("Select DISTRICT_ID,DISTRICT_NAME from tbldistrict  where PROVINCE_ID = " & Me.CboProvince.SelectedValue.ToString & " and AMPHUR_ID = " & Me.cboAMPHUR.SelectedValue.ToString, "Dataset6", "tbldistrict")

            With Me.cboTmb
                .DropDownStyle = ComboBoxStyle.DropDownList
                .DataSource = DSTmb.Tables(0)
                .DisplayMember = "DISTRICT_NAME"
                .ValueMember = "DISTRICT_ID"
            End With
            Cboonload = False
            On_load = False

        Catch ex As Exception

        End Try
    End Sub
    Sub Load_Agent()

        Dim ColNo As New DataGridViewTextBoxColumn
        Dim ColAgentName As New DataGridViewTextBoxColumn
        Dim ColAgentType As New DataGridViewComboBoxColumn
        Dim ColContactNumber As New DataGridViewTextBoxColumn
        Dim colNote As New DataGridViewTextBoxColumn

        ColNo.HeaderText = "รหัส"
        ColNo.Width = 40
        ColNo.DataPropertyName = "vendor_agent_id"
        ColNo.ReadOnly = True

        ColAgentName.HeaderText = "ชื่อตัวแทนจำหน่าย"
        ColAgentName.Width = 200
        ColAgentName.DataPropertyName = "vendor_agent_name"
        ColAgentName.Name = "vendor_agent_name"


        ColAgentType.HeaderText = "ประเภท"
        ColAgentType.Width = 100
        ColAgentType.DataPropertyName = "vendor_agent_type"
        ColAgentType.Name = "vendor_agent_type"

        Strsql = "select vendor_agent_type_id,vendor_agent_type_name from vendoragenttype order by vendor_agent_type_id"
        Dim DTAgentType As New DataTable

        ColAgentType.DataSource = mysql.GetMYSQLDataTable(Strsql, "vendoragenttype")
        ColAgentType.DisplayMember = "vendor_agent_type_name"
        ColAgentType.ValueMember = "vendor_agent_type_id"



        ColContactNumber.HeaderText = "เบอร์โทรศัพท์"
        ColContactNumber.Width = 100
        ColContactNumber.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        ColContactNumber.DataPropertyName = "vendor_agent_contactnumber"

        colNote.HeaderText = "Note"
        colNote.Width = 150
        colNote.DataPropertyName = "vendor_agent_note"


        With GridAgent
            .AutoGenerateColumns = False
            .Columns.Add(ColNo)
            .Columns.Add(ColAgentName)
            .Columns.Add(ColAgentType)
            .Columns.Add(ColContactNumber)
            .Columns.Add(colNote)
            Dim DS As New DSWHIS

            Strsql = "Select vendor_agent_id,vendor_agent_name,vendor_agent_type,vendor_agent_contactnumber,vendor_agent_note from vendoragent"
            If Me.New_Entry_State = False Then
                Strsql = Strsql & "    where vendor_companyid = " & Me.TxtID.Text
            Else
                Strsql = Strsql & "    where vendor_companyid = 0"
            End If

            Strsql = Strsql & " order by vendor_agent_name"
            Dim MyConn As New MySqlConnection
            MyConn.ConnectionString = ClsSQLhelper.ConnMysql
            DA_AGENT = New MySqlDataAdapter(Strsql, MyConn)
            Dim MyCMDBuilder As New MySqlCommandBuilder(DA_AGENT)
            DA_AGENT.Fill(DSWHIS, "vendoragent")
            .DataSource = DSWHIS.Tables("vendoragent")

        End With



    End Sub
    Sub Load_Products_list()

        Dim ColNo As New DataGridViewTextBoxColumn
        Dim ColVendor As New DataGridViewTextBoxColumn
        Dim ColProductName As New DataGridViewComboBoxColumn
        Dim ColContainunit As New DataGridViewTextBoxColumn
        Dim ColPrice As New DataGridViewTextBoxColumn
        Dim colNote As New DataGridViewTextBoxColumn
        Dim colBand As New DataGridViewTextBoxColumn


        ColNo.HeaderText = "ลำดับ"
        ColNo.Width = 40
        ColNo.DataPropertyName = "RowNo"

        With ColVendor
            .Visible = False
            .DataPropertyName = "vendorid"

        End With

        With colBand
            .HeaderText = "ตราสินค้า"
            .Width = 100
            .DataPropertyName = "vendor_product_band"
            ColPrice.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
        ColProductName.HeaderText = "ชื่อวัสดุ"
        ColProductName.Width = 200
        ColProductName.DataPropertyName = "vendor_product_id"
        Strsql = "select itemnumber,concat(item.itemname,'  (',section.section_name,') ') as itemname from item left join section on item.section = section.section_id order by item.section,itemname"
        Dim DTItem As New DataTable

        ColProductName.DataSource = mysql.GetMYSQLDataTable(Strsql, "item")
        ColProductName.DisplayMember = "itemname"
        ColProductName.ValueMember = "itemnumber"


        ColContainunit.HeaderText = "หน่วยบรรจุ"
        ColContainunit.Width = 100
        ColContainunit.DataPropertyName = "vendor_product_containunit"
        ColContainunit.ReadOnly = True
        ColContainunit.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

        ColPrice.HeaderText = "ราคา"
        ColPrice.Width = 100
        ColPrice.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        ColPrice.DataPropertyName = "vendor_product_price"

        colNote.HeaderText = "Note"
        colNote.Width = 150
        colNote.DataPropertyName = "vendor_product_note"

        With GridProductlist


            Dim DS As New DSWHIS
            .AutoGenerateColumns = False


            Strsql = "Select vendorid,vendor_product_band,vendor_product_id,vendor_product_containunit,vendor_product_price,vendor_product_note from vendorproducts"
            If Me.New_Entry_State = False Then
                Strsql = Strsql & " where vendorid = " & Me.TxtID.Text
            Else
                Strsql = Strsql & " where vendorid = 0"
            End If
            Dim MyConn As New MySqlConnection
            MyConn.ConnectionString = ClsSQLhelper.ConnMysql
            DA_Products = New MySqlDataAdapter(Strsql, MyConn)
            Dim MyCMDBuilder As New MySqlCommandBuilder(DA_Products)
            DA_Products.Fill(DSWHIS, "vendorproducts")
            .DataSource = DSWHIS.Tables("vendorproducts")

            .Columns.Add(ColNo)
            .Columns.Add(colBand)
            .Columns.Add(ColProductName)
            .Columns.Add(ColContainunit)
            .Columns.Add(ColPrice)
            .Columns.Add(colNote)
            .Columns.Add(ColVendor)
        End With
    End Sub
    Private Function TrapKey(ByVal KCode As String) As Boolean
        If (KCode >= 48 And KCode <= 57) Or KCode = 8 Then
            TrapKey = False
        Else
            TrapKey = True
        End If
    End Function


#End Region

    Private Sub BtnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCancel.Click
        Me.Dispose()
    End Sub

    Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click



        Try
            If MsgBox("คุณต้องการบันทึกข้อมูลหรือไม่", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "ยืนยันการบันทึก") = MsgBoxResult.No Then Exit Sub

            'Dim DT As New DataTable

            If New_Entry_State = True Then
                If Validate_VendorDetail() = False Then Exit Sub
                Strsql = "Select Count(*) from vendorcompany where companyname= '" & Me.txtCompanyname.Text & "'"

                If mysql.MySQLExecuteScalar(Strsql) < 0 Then Exit Sub

                Strsql = "Insert into vendorcompany (vendorid,companycode,companyname,address1,tmppart,amppart,chwpart,zipcode,tel,fax,note,active,siteid,taxid,contactname) "
                Strsql = Strsql & " values (" & Me.TxtCode.Text & "," & Me.TxtCode.Text & ",'" & Me.txtCompanyname.Text & "', '" & Me.TxtAdd1.Text & "'," & Me.cboTmb.SelectedValue & "," & Me.cboAMPHUR.SelectedValue & " , " & Me.CboProvince.SelectedValue
                Strsql = Strsql & ", '" & Me.TxtZipcode.Text & "', '" & Me.TxtTel1.Text & "','" & Me.TxtFax1.Text & "'"
                Strsql = Strsql & ",'" & Me.TxtNote.Text & "'," & Me.ChkActive.Checked & "," & Me.CboSite.SelectedValue & " ,'" & Me.TxtTaxID.Text & "','" & Me.TxtContactname.Text & "')"

                mysql.MySQLExecute(Strsql)
                'Strsql = "Select companyid from vendorcompany order by companyid desc limit 1"
                'Me.TxtID.Text = mysql.MySQLExecuteScalar(Strsql)


                New_Entry_State = False

                Me.BtnDelete.Enabled = True
                frmSupplyManagement.Load_Data()
                MsgBox("เพิ่มข้อมูลเข้าเรียบร้อยแล้ว", MsgBoxStyle.Information)
            Else

                Strsql = "update vendorcompany set companycode=" & Me.TxtCode.Text & ",contactname = '" & Me.TxtContactname.Text & "',companytype=" & Me.CboCompanyType.SelectedValue & ",companyname= '" & Me.txtCompanyname.Text & "',taxid = '" & Me.TxtTaxID.Text & "', address1 = '" & Me.TxtAdd1.Text & "',tmppart = " & Me.cboTmb.SelectedValue & ",amppart = " & Me.cboAMPHUR.SelectedValue
                Strsql = Strsql & " ,chwpart = " & Me.CboProvince.SelectedValue & ",zipcode = '" & Me.TxtZipcode.Text & "',tel = '" & Me.TxtTel1.Text & "',fax = '" & Me.TxtFax1.Text & "'"
                Strsql = Strsql & " , note = '" & Me.TxtNote.Text & "' ,active = " & Me.ChkActive.Checked
                Strsql = Strsql & " where companyid = '" & Me.TxtID.Text.Trim & "'"

                mysql.MySQLExecute(Strsql)
                DA_AGENT.Update(DSWHIS.Tables("vendoragent"))
                DA_Products.Update(DSWHIS.Tables("vendorproducts"))

                frmSupplyManagement.Load_Data()
                MsgBox("ปรับปรุงข้อมูลเข้าเรียบร้อยแล้ว", MsgBoxStyle.Information)

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub frmSupplyInfo_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Load_ComboboxData_site()
        Load_ComboboxData_CompanyType()
        Load_AddressData()
        Load_VendorData()
        Load_Agent()
        Load_Products_list()
    End Sub


    Private Sub CboProvince_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CboProvince.SelectedIndexChanged
        Try
            If Cboonload = True Then Exit Sub
            Dim ds7, ds8 As New DataSet
            ds7 = mysql.GetMYSQLDataset("Select AMPHUR_ID,AMPHUR_NAME from tblAMPHUR where PROVINCE_ID = " & Me.CboProvince.SelectedValue.ToString, "Dataset7", "tblAMPHUR")
            With Me.cboAMPHUR
                .DropDownStyle = ComboBoxStyle.DropDownList
                .DataSource = ds7.Tables(0)
                .DisplayMember = "AMPHUR_NAME"
                .ValueMember = "AMPHUR_ID"
            End With
            ds8 = mysql.GetMYSQLDataset("Select DISTRICT_ID,DISTRICT_NAME from tbldistrict  where PROVINCE_ID = " & Me.CboProvince.SelectedValue.ToString & " and AMPHUR_ID = " & Me.cboAMPHUR.SelectedValue.ToString, "Dataset8", "tbldistrict")
            With Me.cboTmb
                .DropDownStyle = ComboBoxStyle.DropDownList
                .DataSource = ds8.Tables(0)
                .DisplayMember = "DISTRICT_NAME"
                .ValueMember = "DISTRICT_ID"
            End With
        Catch ex As Exception
            'MsgBox(Err.Number & " : " & ex.Message)
        End Try
    End Sub
    Private Sub cboAMPHUR_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboAMPHUR.SelectedIndexChanged
        Try
            If Cboonload = True Then Exit Sub
            Dim ds9 As New DataSet
            ds9 = mysql.GetMYSQLDataset("Select DISTRICT_ID,DISTRICT_NAME from tbldistrict  where PROVINCE_ID = " & Me.CboProvince.SelectedValue.ToString & " and AMPHUR_ID = " & Me.cboAMPHUR.SelectedValue.ToString, "Dataset6", "tbldistrict")
            With Me.cboTmb
                .DropDownStyle = ComboBoxStyle.DropDownList
                .DataSource = ds9.Tables(0)
                .DisplayMember = "DISTRICT_NAME"
                .ValueMember = "DISTRICT_ID"
            End With
        Catch ex As Exception

        End Try
    End Sub


    Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
        If Me.New_Entry_State = True Then Exit Sub
        If MsgBox("คุณต้องการลบข้อมูลใช่หรือไม่", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "ยืนยัน") = MsgBoxResult.No Then Exit Sub
        Strsql = "Delete vendor where companyid = " & Me.TxtID.Text
        mysql.MySQLExecute(Strsql)

    End Sub
    Private Sub TxtCode_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtCode.KeyPress
        e.Handled = TrapKey(Asc(e.KeyChar))
    End Sub

    Private Sub GridProductlist_CellBeginEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles GridProductlist.CellBeginEdit
        If e.ColumnIndex <> 2 Then Exit Sub
        With Me.GridProductlist
            If IsDBNull(.Rows(.CurrentCell.RowIndex).Cells(1).Value) = True Then .Rows(.CurrentCell.RowIndex).Cells(1).Value = Me.txtCompanyname.Text
            .Rows(.CurrentCell.RowIndex).Cells(6).Value = Me.TxtID.Text
            .Rows(.CurrentCell.RowIndex).Cells(4).Value = 0
        End With
    End Sub

    Private Sub GridProductlist_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridProductlist.CellClick
        With GridProductlist
            If IsDBNull(.Rows(.CurrentCell.RowIndex).Cells(2).Value) = True Then Exit Sub
            If .CurrentCell.ColumnIndex = 3 Then

                Dim colUnit As New DataGridViewComboBoxCell
                Strsql = "select  containunit from itemcontainunit where itemid = '" & .Rows(.CurrentCell.RowIndex).Cells(2).Value & "' "
                Dim DTUnit As New DataTable
                DTUnit = mysql.GetMYSQLDataTable(Strsql, "itemcontainunit")

                colUnit.Items.Clear()
                For i As Integer = 0 To DTUnit.Rows.Count - 1
                    colUnit.Items.Add(DTUnit.Rows(i).Item(0))
                Next
                .Rows(.CurrentCell.RowIndex).Cells(3) = colUnit
                .Rows(.CurrentCell.RowIndex).Cells(3).ReadOnly = False

            End If
        End With
    End Sub


    Private Sub GridProductlist_CurrentCellDirtyStateChanged1(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridProductlist.CurrentCellDirtyStateChanged
        Try
            With GridProductlist
                If .IsCurrentCellDirty = False Then Return

                .CommitEdit(DataGridViewDataErrorContexts.Commit)

                If .CurrentCell.ColumnIndex = 2 Then

                    Dim colUnit As New DataGridViewComboBoxCell
                    Strsql = "select  containunit from itemcontainunit where itemid = '" & .Rows(.CurrentCell.RowIndex).Cells(2).Value & "' "
                    Dim DTUnit As New DataTable
                    DTUnit = mysql.GetMYSQLDataTable(Strsql, "itemcontainunit")

                    colUnit.Items.Clear()
                    For i As Integer = 0 To DTUnit.Rows.Count - 1
                        colUnit.Items.Add(DTUnit.Rows(i).Item(0))
                    Next
                    .Rows(.CurrentCell.RowIndex).Cells(3) = colUnit
                    .Rows(.CurrentCell.RowIndex).Cells(3).ReadOnly = False
                    .Rows(.CurrentCell.RowIndex).Cells(3).Value = DTUnit.Rows(0).Item(0)

                Else

                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub TxtCode_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtCode.TextChanged

    End Sub

    Private Sub Panel1_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles Panel1.Paint

    End Sub

    Private Sub GridProductlist_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridProductlist.CellContentClick

    End Sub
End Class